﻿@model doufu.Model.TableInfo

@{
    ViewBag.Title = "title";
    Layout = null;

    var columnType = new List<string> { "INTEGER", "INT", "NVARCHAR", "DATETIME", "UNIQUEIDENTIFIER", "DECIMAL" }
        .Select(x => new SelectListItem { Text = x, Value = x }).ToArray();

    var newTable = string.IsNullOrEmpty(Model.name);
    
}

<div>
    表名：
    @if (!newTable)
    {
        @Html.DisplayFor(x => x.name) <span id="ReNameTable" class="hide"> @Html.TextBoxFor(x => x.name) <a class="btn btn-primary" onclick="ReNameTable('@Model.name')">确定</a> </span> <a class="btn btn-small" onclick="$('#ReNameTable').show()">改表名</a> <a class="btn btn-small" href="#EditTable" onclick="Edit('')">添加列</a> 
        <table class="TSQLREsult">
            <tr>
                <th>序号</th>
                <th>字段</th>
                <th>类型</th>
                <th>长度</th>
                <th>默认值</th>
                <th>非空</th>
                <th>主键</th>
                <th>自增</th>
                <th>操作</th>
            </tr>
            @if (Model != null && Model.Columnses != null)
            {
                foreach (var column in Model.Columnses)
                {
                <tr>
                    <td>@column.cid</td>
                    <td>@column.name</td>
                    <td>@column.type</td>
                    <td>@column.length</td>
                    <td>@column.dflt_value</td>
                    <td>
                        <input type="checkbox" checked="@column.notnull" disabled="disabled" /></td>
                    <td>
                        <input type="checkbox" checked="@column.pk" disabled="disabled" /></td>
                    <td>
                        <input type="checkbox" checked="@column.auto_increment" disabled="disabled" /></td>
                    <td><a href="#EditTable" onclick="Edit('@column.name')" class="btn btn-mini">修改</a> <a class="btn btn-mini" onclick="Del('@column.name')">删除</a></td>
                </tr>
                }
            }
        </table>
    }
    else
    {
        <span id="ReNameTable">@Html.TextBoxFor(x => x.name) </span> <div class="btn-group">
            <a class="btn btn-small" onclick="AddFields(1)">添加列</a>
            <button class="btn dropdown-toggle btn-small" data-toggle="dropdown">
                <span class="caret"></span>
            </button>
            <ul class="dropdown-menu">
                @for (var i = 1; i <= 10; i++)
                {<li><a onclick="AddFields(@i)">@(i)列</a></li>}
            </ul>
        </div>
    }
    <script type="text/javascript">
       
        function GetV(tb_name) {
            return $.grep(@Html.Raw(Model.columns_array) , function(n,i){
                return n.name == tb_name;
            });
        }

        function Edit(tb_name) {
            var objs=GetV(tb_name);
            var obj;
            if(objs.length>0) {
                obj = objs[0];
            }
            else {
                obj = {name:"",type:"",length:"",dflt_value:"",notnull:false ,pk:false,auto_increment:false};
            }
            $("#EditTable #name").val(obj.name);
            $("#EditTable #old_name").val(obj.name);
            $("#EditTable #type").val(obj.type);
            $("#EditTable #length").val(obj.length);
            $("#EditTable #dflt_value").val(obj.dflt_value);
            $("#EditTable #notnull").prop("checked",obj.notnull);
            $("#EditTable #pk").prop("checked", obj.pk);
            $("#EditTable #auto_increment").prop("checked", obj.auto_increment);
        }

        function AddFields(num) {
            for(var i=0;i<num;i++) {
                $("#EditTable .field-row").parent().append("<tr>" + $("#EditTable .field-row").html() + "</tr>");
            }
        }
        function SaveTable() {
            if(@(newTable.ToString().ToLower()) == true) {
                var obj = {
                    name:$("#EditTable .field-row #name").val(),
                    type:$("#EditTable .field-row #type").val(),
                    length:$("#EditTable .field-row #length").val(),
                    dflt_value:$("#EditTable .field-row #dflt_value").val(),
                    notnull:$("#EditTable .field-row #notnull").prop("checked"),
                    pk:$("#EditTable .field-row #pk").prop("checked"),
                    auto_increment:$("#EditTable .field-row #auto_increment").prop("checked")
                };
                if(obj.length.length>0) {
                    obj.type = obj.type+"("+obj.length+")";
                }

                var sql = "CREATE TABLE [" + $("#ReNameTable #name").val() + "]([" + obj.name + "] " + obj.type + (obj.notnull ? " NOT " : "") + " NULL "+(obj.dflt_value.length>0?" DEFAULT "+obj.dflt_value:"") + (obj.pk ? " PRIMARY KEY " : "") + (obj.auto_increment ? " AUTOINCREMENT " : "")+")";
                ExcuteSQL(sql);
            }
            else {
                var obj = {
                    name:$("#EditTable .field-row #name").val(),
                    type:$("#EditTable .field-row #type").val(),
                    length:$("#EditTable .field-row #length").val(),
                    dflt_value:$("#EditTable .field-row #dflt_value").val(),
                    notnull:$("#EditTable .field-row #notnull").prop("checked"),
                    pk:$("#EditTable .field-row #pk").prop("checked"),
                    auto_increment:$("#EditTable .field-row #auto_increment").prop("checked")
                };
                if(obj.length.length>0) {
                    obj.type = obj.type+"("+obj.length+")";
                }

                var sql = "ALTER TABLE [" + $("#ReNameTable #name").val() + "] ADD COLUMN [" + obj.name + "] " + obj.type + (obj.notnull ? " NOT " : "") + " NULL "+(obj.dflt_value.length>0?" DEFAULT "+obj.dflt_value:"") + (obj.pk ? " PRIMARY KEY " : "") + (obj.auto_increment ? " AUTOINCREMENT " : "");
                ExcuteSQL(sql);
            }
        }
        function ReNameTable(oldName) {
            var sql = "ALTER TABLE ["+oldName+"] RENAME TO ["+$("#ReNameTable #name").val()+"]";
            ExcuteSQL(sql);
        }

        function ExcuteSQL(sql) {
            console.log(sql);
            $.post('@Url.Action("Excute")',{sql:sql},function(r) {
                console.log(r);
            });
        }
    </script>
    <table class="TSQLREsult" id="EditTable">
        <tr>
            <th>字段</th>
            <th>类型</th>
            <th>长度</th>
            <th>默认值</th>
            <th>非空</th>
            <th>主键</th>
            <th>自增</th>
        </tr>
        <tr class="field-row">
            <td>@Html.Hidden("old_name") @Html.TextBox("name", "", new Dictionary<string, object> { { "class", "span2" }, { "data-bind", "text: name" } })</td>
            <td>@Html.DropDownList("type", columnType, new Dictionary<string, object> { { "class", "span2" }, { "data-bind", "text: type" } })</td>
            <td>@Html.TextBox("length", null, new { @class = "span1" })</td>
            <td>@Html.TextBox("dflt_value", null, new { @class = "span2" })</td>
            <td>@Html.CheckBox("notnull")</td>
            <td>@Html.CheckBox("pk")</td>
            <td>@Html.CheckBox("auto_increment")</td>
        </tr>

    </table>
    <input type="button" name="btnSave" value="@(newTable ? "创建表" : "修改字段")" class="btn btn-primary" onclick="SaveTable()" />
    <div class="well">
        @Html.DisplayFor(x => x.sql_info)
    </div>
</div>
